package com.clouds.wms.util;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.regex.Pattern;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;



public class ExcelUtil {

	private Log log = LogFactory.getLog(getClass());
	
	private String sheetName;

	public Workbook writeExcel2003(List<List<Object>> datas) {
		return writeExcel(new HSSFWorkbook(), datas);
	}

	public Workbook writeExcel2007(List<List<Object>> datas) {
		return writeExcel(new XSSFWorkbook(), datas);
	}

	private Workbook writeExcel(Workbook workbook, List<List<Object>> datas) {
		// 设置样式
		/*
		 * CellStyle cellStyle = wb.createCellStyle();// 创建样式
		 * cellStyle.setDataFormat
		 * (wb.createDataFormat().getFormat(dataFormat));// 日期格式
		 * cellStyle.setAlignment(CellStyle.ALIGN_CENTER);// 居中
		 */
		// 创建�?��新的表\并创建名�?
		Sheet sheet = null == sheetName ? sheet = workbook.createSheet() : workbook.createSheet(sheetName);
		for(int i = 0; i < datas.size(); i++) { // 遍历数据
			List<Object> objList = datas.get(i);
			Row row = sheet.createRow(i);
			int objListSize = objList.size();
			for (int x = 0; x < objListSize; x++) {
				Cell cell = row.createCell(x);
				Object value = objList.get(x);
				if (value instanceof String) {
					cell.setCellValue(value.toString());
				} else if (value instanceof Double) {
					cell.setCellValue((Double)value);
				} else if (value instanceof Integer) {
					cell.setCellValue((Integer)value);
				} else if (value instanceof Long) {
					cell.setCellValue((Long)value);
				} else if (value instanceof Float) {
					cell.setCellValue((Float)value);
				} else if (value instanceof Boolean) {
					cell.setCellValue((Boolean)value);
				} else if (value instanceof java.util.Date || value instanceof java.sql.Date) {
					cell.setCellValue(Function.getDateStringByPattern((Date)value, "yyyy-MM-dd HH:mm:ss"));
				}
				// 设置内容样式
				// cell.setCellStyle(cellStyle);
				// 自动调整列宽
				sheet.autoSizeColumn(x);
			}
		}
		return workbook;
	}

	public List<List<Object>> readExcel2003(String path) throws FileNotFoundException, IOException {
		HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(path));
		List<List<Object>> resultList = new ArrayList<List<Object>>();
		//获得该工作区的第�?��sheet
		HSSFSheet sheet = workbook.getSheetAt(0);
		//总共有多少行,�?�?��
		int totalRows = sheet.getLastRowNum();
		for(int i = 1; i <= totalRows; i++) {// 行数循环,第一行头部去�?
			List<Object> objList = new ArrayList<Object>();
			HSSFRow row = sheet.getRow(i);
			if(row == null) {
				continue;
			}
			int totleCells = row.getLastCellNum();
			for(int x = 0; x < totleCells; x++) {
				HSSFCell cell = row.getCell(x);
				if(cell != null) {
					if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
						objList.add(cell.getStringCellValue());
					} else if (cell.getCellType()  == Cell.CELL_TYPE_NUMERIC) {
						objList.add(cell.getNumericCellValue());
					} else if(cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
						objList.add(cell.getBooleanCellValue());
					}
				}
			}
			resultList.add(objList);
		}
		return resultList;
	}

	public List<List<Object>> readExcel2007(String path) {
		XSSFWorkbook workbook = null;
		try {
			workbook = new XSSFWorkbook(new FileInputStream(path));
		} catch (FileNotFoundException e) {
			log.error(e.getMessage(), e);
			return null;
		} catch (IOException e) {
			log.error(e.getMessage(), e);
			return null;
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
		List<List<Object>> resultList = new ArrayList<List<Object>>();
		//获得该工作区的第sheet
		XSSFSheet sheet = workbook.getSheetAt(0);
		//总共有多少行
		int totalRows = sheet.getLastRowNum();
		for(int i = 1; i <= totalRows; i++) {// 行数循环,第一行头部去
			List<Object> objList = new ArrayList<Object>();
			XSSFRow row = sheet.getRow(i);
			if(row == null) {
				continue;
			}
			int totleCells = row.getLastCellNum();
			for(int x = 0; x < totleCells; x++) {
				XSSFCell cell = row.getCell(x);
				if(cell != null) {
					if(cell.getCellType() == Cell.CELL_TYPE_STRING) {
						objList.add(cell.getStringCellValue());
					} else if(cell.getCellType()  == Cell.CELL_TYPE_NUMERIC) {
						if(HSSFDateUtil.isCellDateFormatted(cell)) {// 是否是日期格
							objList.add(Function.getDateString(cell.getDateCellValue()));
						} else {
							String value ="";
							double strCell = cell.getNumericCellValue();
							DecimalFormat formatCell = (DecimalFormat) NumberFormat.getPercentInstance();     
				            formatCell.applyPattern("0");
				            value = formatCell.format(strCell);
				            if(Double.parseDouble(value)!=strCell){
				            	formatCell.applyPattern(Double.toString(strCell));
				                value = formatCell.format(strCell);
				            }
							objList.add(value);
						}
					} else if(cell.getCellType()  == Cell.CELL_TYPE_BLANK) {
						objList.add("");
					}
				} else {
					objList.add("");
				}
			}
			resultList.add(objList);
		}
		return resultList;
	}
	public List<List<Object>> readExcelCD(String path) {
		XSSFWorkbook workbook = null;
		try {
			workbook = new XSSFWorkbook(new FileInputStream(path));
		} catch (FileNotFoundException e) {
			log.error(e.getMessage(), e);
			return null;
		} catch (IOException e) {
			log.error(e.getMessage(), e);
			return null;
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}
		List<List<Object>> resultList = new ArrayList<List<Object>>();
		//获得该工作区的第sheet
		XSSFSheet sheet = workbook.getSheetAt(0);
		//总共有多少行
		int totalRows = sheet.getLastRowNum();
		for(int i = 1; i <= totalRows; i++) {// 行数循环,第一行头部去
			List<Object> objList = new ArrayList<Object>();
			XSSFRow row = sheet.getRow(i);
			if(row == null) {
				continue;
			}
			
			int totleCells = row.getLastCellNum();
			if(totleCells!=13){
				return null;
			}
			for(int x = 0; x < totleCells; x++) {
				XSSFCell cell = row.getCell(x);
				if(cell != null) {
					if(x==0){
						
					}
					if(cell.getCellType() == Cell.CELL_TYPE_STRING) {
						objList.add(cell.getStringCellValue());
					} else if(cell.getCellType()  == Cell.CELL_TYPE_NUMERIC) {
						if(HSSFDateUtil.isCellDateFormatted(cell)) {// 是否是日期格
							objList.add(Function.getDateString(cell.getDateCellValue()));
						} else {
							objList.add(cell.getNumericCellValue());
						}
					} else if(cell.getCellType()  == Cell.CELL_TYPE_BLANK) {
						objList.add("");
					}
				} else {
					objList.add("");
				}
			}
			resultList.add(objList);
		}
		return resultList;
	}

	public String getSheetName() {
		return sheetName;
	}

	public void setSheetName(String sheetName) {
		this.sheetName = sheetName;
	}
	
	public Object[] readExcel(String path,ExcelImportChecker checker)
	{
		Workbook workbook = null;
		List<List<Object>> resultList = null;
		String errorMsg = null;
		try {
			path = path.trim();
			FileInputStream fis = new FileInputStream(path);
			if(path.endsWith(".xlsx"))
			{
				workbook = new XSSFWorkbook(fis);
			}else if(path.endsWith(".xls"))
			{
				workbook = WorkbookFactory.create(fis);
			}

			resultList = new ArrayList<List<Object>>();
			Sheet sheet = workbook.getSheetAt(0);
			int totalRows = sheet.getLastRowNum();
			for(int i = 1; i <= totalRows; i++) {// 行数循环,第一行头部去
				List<Object> objList = new ArrayList<Object>();
				Row row = sheet.getRow(i);
				if(row == null) {
					continue;
				}
				errorMsg = checker.rowCheck(row);
				
				if(errorMsg!=null)
				{
					throw new ExcelDataException(errorMsg);
				}
				int totleCells = row.getLastCellNum();
				for(int x = 0; x < totleCells; x++) {
					Cell cell = row.getCell(x);
					if(cell != null) {
						
						if(cell.getCellType()  == Cell.CELL_TYPE_BLANK) 
						{
							objList.add("");
						}else
						{
							switch(checker.getCellType(x))
							{
								case ExcelChecker.CellType.STRING:
									objList.add(cell.toString());
									break;
								case ExcelChecker.CellType.NUMBER:
								try {
									objList.add(cell.getNumericCellValue());
								} catch (Exception e) {
									throw new ExcelDataException("第"+(i+1)+"行,第"+(x+1)+"列，“"+cell.toString()+"”，数据格式有误，系统要求传入数值类型，而此值可能不是数值类型.");
								}
									break;
								case ExcelChecker.CellType.DATE:
								try {
									objList.add(Function.getDateString(cell.getDateCellValue()));
								} catch (Exception e) {
									throw new ExcelDataException("第"+(i+1)+"行,第"+(x+1)+"列，“"+cell.toString()+"”，数据格式有误，系统要求传入日期格式，而此值可能不是所要求的日期格式.");
								}
									break;
								default:
									throw new ExcelDataException("Unknown data format.");
							}
						}
					} else {
						objList.add("");
					}
				}
				resultList.add(objList);
			}
		} catch (FileNotFoundException e) {
			errorMsg = "文件上传的路径有误："+e.toString();
			e.printStackTrace();
		} catch (IOException e) {
			errorMsg = "文件读取错误："+e.toString();
			e.printStackTrace();
		} catch (InvalidFormatException e) {
			errorMsg = "文件格式解析错误："+e.toString();
			e.printStackTrace();
		} catch (ExcelDataException e) {
			errorMsg = "数据错误提示："+e.toString();
			e.printStackTrace();
		}
		return new Object[]{resultList,errorMsg};
	}
	
	private static String NumberRegex = "^\\d+(\\.\\d+)?$";
	private static Pattern NumberPattern = Pattern.compile(NumberRegex);
	public static boolean isNumber(String str)
	{
		return NumberPattern.matcher(str).matches();
	}
	public static String[] getColStringValue(List<Object> colList,int colNum,int rowNum)
	{
		String retObj = null;
		String errorMsg = null;
		try {
			Object obj = colList.get(colNum);
			if(obj!=null)
			{
				retObj = obj.toString().trim();
				
			}else
			{
				errorMsg = (rowNum+1)+"：行,"+colNum+"列，值为空";
			}
			
		} catch (Exception e) {
			e.printStackTrace();
			errorMsg = (rowNum+1)+"：行,"+colNum+"列，数据发生错误："+e.toString();
		}
		return new String[]{retObj,errorMsg};
	}
	public static Object[] getColDoubleValue(List<Object> colList,int colNum,int rowNum)
	{
		double retObj = -1;
		String errorMsg = null;
		String objTmp = null;
		try {
			Object obj = colList.get(colNum);
			if(obj!=null)
			{
				objTmp = obj.toString().trim();
				if(isNumber(objTmp))
				{
					retObj = Double.parseDouble(objTmp);
				}else
				{
					errorMsg = (rowNum+1)+"：行,"+colNum+"列，值不是数值类型";
				}
			}else
			{
				errorMsg = (rowNum+1)+"：行,"+colNum+"列，值为空";
			}
			
		} catch (Exception e) {
			e.printStackTrace();
			errorMsg = (rowNum+1)+"：行,"+colNum+"列，数据发生错误："+e.toString();
		}
		return new Object[]{retObj,errorMsg};
	}
	public static Object[] getColIntegerValue(List<Object> colList,int colNum,int rowNum)
	{
		int retObj = -1;
		String errorMsg = null;
		String objTmp = null;
		try {
			Object obj = colList.get(colNum);
			if(obj!=null)
			{
				objTmp = obj.toString().trim();
				if(isNumber(objTmp))
				{
					Double retObj1 = Double.parseDouble(objTmp);
					retObj = retObj1.intValue();
				}else
				{
					errorMsg = (rowNum+1)+"：行,"+colNum+"列，值不是数值类型";
				}
			}else
			{
				errorMsg = (rowNum+1)+"：行,"+colNum+"列，值为空";
			}
			
		} catch (Exception e) {
			e.printStackTrace();
			errorMsg = rowNum+"：行,"+colNum+"列，数据发生错误："+e.toString();
		}
		return new Object[]{retObj,errorMsg};
	}
	public static Object[] getColFloatValue(List<Object> colList,int colNum,int rowNum)
	{
		float retObj = -1;
		String errorMsg = null;
		String objTmp = null;
		try {
			Object obj = colList.get(colNum);
			if(obj!=null)
			{
				objTmp = obj.toString().trim();
				if(isNumber(objTmp))
				{
					retObj = Float.parseFloat(objTmp);
				}else
				{
					errorMsg = (rowNum+1)+"：行,"+colNum+"列，值不是数值类型";
				}
			}else
			{
				errorMsg = (rowNum+1)+"：行,"+colNum+"列，值为空";
			}
			
		} catch (Exception e) {
			e.printStackTrace();
			errorMsg = (rowNum+1)+"：行,"+colNum+"列，数据发生错误："+e.toString();
		}
		return new Object[]{retObj,errorMsg};
	}

	public static void main(String[] args) throws FileNotFoundException, IOException {
		List<List<Object>> datas = new ArrayList<List<Object>>();
		List<Object> list1 = new ArrayList<Object>();
		list1.add("用户");
		list1.add("密码");
		list1.add("创建日期");
		List<Object> list2 = new ArrayList<Object>();
		list2.add("xiaomaha");
		list2.add("1234567qq");
		list2.add(new Date());
		List<Object> list3 = new ArrayList<Object>();
		list3.add("xiaomaha");
		list3.add("1234567qq");
		list3.add(1111111111);
		datas.add(list1);
		datas.add(list2);
		datas.add(list3);
		ExcelUtil et = new ExcelUtil();
		et.setSheetName("测试");
		Workbook workbook = et.writeExcel2007(datas);
		String filename = "d://workbook2.xlsx";
		try {
			FileOutputStream out = new FileOutputStream(filename);
			workbook.write(out);
			out.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		String path = "d://workbook2.xlsx";
		ExcelUtil excelUtil = new ExcelUtil();
		List<List<Object>> resultList = excelUtil.readExcel2007(path);
		for(List<Object> objList : resultList) {
			for(Object obj : objList) {
				System.out.println(obj);
			}
		}
	}
}
